package cn.getech.data.metadata.db;

import cn.getech.data.development.constant.ConnectTypeEnum;
import cn.getech.data.intelligence.common.exception.RRException;
import cn.getech.data.metadata.constant.TaskStatusEnum;
import cn.getech.data.metadata.entity.meta.*;
import cn.getech.data.metadata.service.*;
import cn.getech.data.metadata.utils.MetadataContextUtil;
import cn.getech.data.metadata.utils.SingleConnect;
import cn.getech.system.center.entity.SysSecurityDataLevelEntity;
import cn.getech.system.center.service.SysSecurityDataLevelService;
import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.function.Consumer;
import java.util.function.Function;

public class DBStructure {
    protected Logger log = LoggerFactory.getLogger(DBStructure.class);

    private CollectionTableInfoService collectionTableInfoService;
    private CollectionTableField2Service collectionTableField2Service;
    private CollectionTaskService collectionTaskService;
    private CollectionGenerateService collectionGenerateService;
    private SysSecurityDataLevelService sysSecurityDataLevelService;

    protected String url;
    protected String username;
    protected String password;
    protected String driverClass;
    protected String dbName;
    protected CollectionTask collectionTask;

    protected String columnSql;
    protected String tablesSql;
    protected String rowAndLengthSql;

    public DBStructure(String url, String username, String password, String driverClass, String dbName, CollectionTask collectionTask) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.driverClass = driverClass;
        this.dbName = dbName;
        this.collectionTask = collectionTask;

        this.collectionTableInfoService = MetadataContextUtil.getBean(CollectionTableInfoService.class);
        this.collectionTableField2Service = MetadataContextUtil.getBean(CollectionTableField2Service.class);
        this.collectionTaskService = MetadataContextUtil.getBean(CollectionTaskService.class);
        this.collectionGenerateService = MetadataContextUtil.getBean(CollectionGenerateService.class);
        this.sysSecurityDataLevelService = MetadataContextUtil.getBean(SysSecurityDataLevelService.class);
    }

    protected List<CollectionTableInfo> getTargetTables(List<String> dbNames) {
        List<CollectionTableInfo> targetTables = new ArrayList<>();
        for(String db : dbNames){
            List<CollectionTableInfo> targetDbTables = getTargetTables(db, collectionTask.getOwnModule());
            CollectionUtil.addAll(targetTables, targetDbTables);
        }
        return targetTables;
    }

    /**
    * @Description 获取库中当前的目标表
    * @Author  chengweiping
    * @Date   2020/12/31 10:57
    */
    protected List<CollectionTableInfo> getTargetTables(String db, Integer ownModule) {
        List<CollectionTableInfo> collectionTableInfoList=new ArrayList<>();
        Integer tenantId= collectionTask.getTenantId();
         //  taskType=0 是手动采集   taskType=1是自动采集
        //自动采集 , 数据源-数据库-表  保持数据唯一
        if(collectionTask.getTaskType()==1){
            String souceConf=url;
            //如果当前对象没有数据库,补齐dbName
            if(StringUtils.isBlank(dbName)){
                souceConf=url+db;
            }
            if( ConnectTypeEnum.Oracle.getDriverClass().equals(driverClass)){
                souceConf=souceConf+"/"+username; // 对于oracle需要添加用户名过滤
            }
            collectionTableInfoList= collectionTableInfoService.list(
                    new QueryWrapper<CollectionTableInfo>()
                            .eq("db_name", db)
                            .eq("souce_conf", souceConf)
                            .eq(tenantId != null, "tenant_id", tenantId)
                            .eq("delete_flag", 0)
                            .orderByDesc("table_name"));
        }else{
            //手动采集 ,模块-数据库-表  保持数据唯一
            collectionTableInfoList= collectionTableInfoService.list(
                    new QueryWrapper<CollectionTableInfo>()
                            .eq("db_name", db)
                            .eq(tenantId != null, "tenant_id", tenantId)
                            .eq("own_module", ownModule)
                            .eq("delete_flag", 0)
                            .orderByDesc("table_name"));
        }
        return collectionTableInfoList;
    }

    private List<CollectionTableField> getTargetTableField(Integer tableId) {
        return collectionTableField2Service.list(
                new QueryWrapper<CollectionTableField>()
                        .eq("table_id", tableId)
                        .eq("delete_flag", 0)
                        .orderByDesc("field_name")
        );
    }

    public void generateTable(long startTime) throws Exception {
        List<String> dbNames = getDbNames(dbName, collectionTask.getDbName());

        //从数据源获取表列表
        List<CollectionTableInfo> sourceTables = getSourceTables(dbNames);
        //从存储的表信息获取列表
        List<CollectionTableInfo> targetTables = getTargetTables(dbNames);

        //添加运行日志
        Function<String, Long> insertRunLog = logContent -> {
            collectionTask.setRunStatus(TaskStatusEnum.SUCCESS.getValue());
            if(collectionTask.getRunTime() == null)
                collectionTask.setRunTime(new Date());
            Long taskLogId = collectionTaskService.insertRunLog(startTime, collectionTask,
                    logContent, collectionTask.getTaskRunLeader());
            return taskLogId;
        };

        //获取表行数和表大小
        Function<CollectionTableInfo,CollectionTableInfo> getRowsAndLength = this::getRowsAndLength;

        SysSecurityDataLevelEntity defaultSecurityDataLevel = null;
        try {
            defaultSecurityDataLevel = sysSecurityDataLevelService.getDefaultTableService();
            //设置默认数据密级，此处的值主要用于字段的数据密级设置、更新表信息时，表字段不会更新这个字段
            if(StringUtils.isNotBlank(defaultSecurityDataLevel.getName())){
                if(CollectionUtil.isNotEmpty(targetTables)){
                    for(CollectionTableInfo collectionTableInfo:targetTables){
                        collectionTableInfo.setSecurityDataLevel(defaultSecurityDataLevel.getName());
                    }
                }
            }
        } catch (Exception e) {
            log.warn("密集色设置错误:{}",e.getMessage());
        }
        //设置表信息
        SysSecurityDataLevelEntity finalDefaultSecurityDataLevel = defaultSecurityDataLevel;
        Consumer<CollectionTableInfo> setTableInfo = tableInfo -> {
            if (tableInfo.getRowsNum() == null)
                tableInfo.setRowsNum(0L);
            if (tableInfo.getTotalSize() == null)
                tableInfo.setTotalSize(0.0);
            tableInfo.setTaskType(1);//录入方式 0 手动采集 1自动采集
            tableInfo.setViewCount(0);
            tableInfo.setTempFlag(0);//是否临时表 1 是,0 否
            tableInfo.setCreateTime(new Date());
            tableInfo.setTableType(3);//外部元数据表
            tableInfo.setCreateUser(collectionTask.getTaskRunLeader());
            tableInfo.setUpdateTime(tableInfo.getCreateTime());
            tableInfo.setUpdateUser(tableInfo.getCreateUser());
            tableInfo.setDeleteFlag(0);//删除 1 是,0 否
            tableInfo.setOwnSystem(collectionTask.getOwnSystem());
            tableInfo.setOwnModule(collectionTask.getOwnModule());
            String sourceConf = url;
            if (StringUtils.isEmpty(dbName)) {
                sourceConf += tableInfo.getDbName();
            }
            if (ConnectTypeEnum.Oracle.getDriverClass().equals(driverClass)) {
                sourceConf = sourceConf + "/" + username; // 对于oracle需要添加用户名过滤
            }
            tableInfo.setTenantId(collectionTask.getTenantId());
            tableInfo.setSouceConf(sourceConf);
            tableInfo.setSourceId(collectionTask.getSourceId().intValue());
            if (finalDefaultSecurityDataLevel != null) {
                tableInfo.setSecurityDataLevel(finalDefaultSecurityDataLevel.getName());
            }
        };

        //获取源表的字段信息
        Function<CollectionTableInfo,List<CollectionTableField>> getSourceColumns = this::getSourceColumns;

        //获取目标表的字段信息
        Function<Integer,List<CollectionTableField>> getTargetTableField = this::getTargetTableField;

        //  修改模块和系统
        for (CollectionTableInfo sourceTable : sourceTables) {
            sourceTable.setOwnSystem(collectionTask.getOwnSystem());
            sourceTable.setOwnModule(collectionTask.getOwnModule());
        }
        collectionGenerateService.generate(sourceTables, targetTables, collectionTask.getInitRole(),collectionTask.getTaskRunLeader(),collectionTask.getId(),
                insertRunLog, getRowsAndLength, setTableInfo, getSourceColumns, getTargetTableField, username);
    }

    private List<String> getDbNames(String sourceDb, String taskDb) throws Exception {
        List<String> dbNames = new ArrayList<>();
        if(StringUtils.isNotEmpty(taskDb)){
            String[] tempDbNames = taskDb.split(",");
            CollectionUtil.addAll(dbNames, tempDbNames);
        } else {
            dbNames.add(sourceDb);
        }
        return dbNames;
    }


    private List<CollectionTableInfo> getSourceTables(List<String> dbNames) throws Exception {
        //从数据源获取表列表
        List<CollectionTableInfo> sourceTables = new ArrayList<>();
        for(String db : dbNames){
            List<CollectionTableInfo> sourceDbTables = getSourceTables(db);
            CollectionUtil.addAll(sourceTables, sourceDbTables);
        }
        return sourceTables;
    }

    protected List<CollectionTableInfo> getSourceTables(String db) throws Exception {
        List<CollectionTableInfo> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = getConnection();
            if (conn == null)
                throw new RRException("获取数据库连接失败");

            ResultSet result;
            if (StringUtils.isNotEmpty(tablesSql)) {
                String sql = tablesSql.replace("#{dbName}", db);
                //oracle专用
                sql = sql.replace("#{userName}", username.toUpperCase());
                Statement st = conn.createStatement();
                result = st.executeQuery(sql);
            } else {
                conn.setCatalog(db);
                String[] types = new String[1];
                types[0] = "TABLE";
                result = conn.getMetaData().getTables(null, null, null, types);
            }
            while (result != null && result.next()) {
                CollectionTableInfo tableInfo = new CollectionTableInfo();
                tableInfo.setDbName(db);
                tableInfo.setTableType(3);
                String tableName = result.getString("TABLE_NAME");
                if (StringUtils.isEmpty(tableName)) {
                    continue;
                }
                tableInfo.setTableName(result.getString("TABLE_NAME"));
                String remarks = result.getString("REMARKS");
                tableInfo.setTableDesc(remarks);
                if (StringUtils.isEmpty(remarks))
                    tableInfo.setTableAlias(tableInfo.getTableName());
                else
                    tableInfo.setTableAlias(remarks);
                list.add(tableInfo);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }

        return list;
    }

    protected List<CollectionTableField> getSourceColumns(CollectionTableInfo tableInfo) {
        List<CollectionTableField> list = new ArrayList<>();
        Connection conn = null;
        try {
            conn = getConnection();

            if (conn == null)
                throw new RRException("获取数据库连接失败");
            ResultSet result;
            //有sql语句走if逻辑，目前只有oracle走这段逻辑、独立写sql
            if (StringUtils.isNotEmpty(columnSql)) {
                String sql = columnSql.replace("#{tableName}", tableInfo.getTableName());
                //oracle专用
                sql = sql.replace("#{userName}", username.toUpperCase());

                Statement st = conn.createStatement();
                result = st.executeQuery(sql);
                while (result != null && result.next()) {
                    String columnName = result.getString("COLUMN_NAME");
                    String typeName = result.getString("TYPE_NAME");
                    CollectionTableField tableField = new CollectionTableField();
                    tableField.setFieldName(columnName);
                    tableField.setFieldType(typeName);
                    tableField.setFieldDesc(result.getString("REMARKS"));
                    //外部采集的数据，肯定没有分区字段
                    tableField.setIsPartition(0);
                    //是否为空字段
                    String  isNullable=result.getString("IS_NULLABLE")==null?null:result.getString("IS_NULLABLE");
                    Integer isNullabelInt=null;
                    if(isNullable!=null&&isNullable.equalsIgnoreCase("N")){
                        isNullabelInt=0;
                    }else{
                        isNullabelInt=1;
                    }
                    //设置是否为空
                    tableField.setFieldIsNull(isNullabelInt);
                    Integer  columnSize=result.getString("COLUMN_SIZE")==null?null:Integer.parseInt(result.getString("COLUMN_SIZE"));
                    //设置字段长度
                    tableField.setFieldLength(columnSize);
                    List<String>  primaryKeyNameList=getPrimayKeyName(conn,tableInfo.getTableName(),this.driverClass);
                    //设置是否主键
                    if (CollectionUtil.isNotEmpty(primaryKeyNameList) && primaryKeyNameList.contains(columnName)) {
                        tableField.setPrimaryKey(1);
                    }else {
                        tableField.setPrimaryKey(0);
                    }
                    list.add(tableField);
                }
            } else {
                //否则通过java jdbc 获取元数据信息
                conn.setCatalog(tableInfo.getDbName());
                result = conn.getMetaData().getColumns(null, null, tableInfo.getTableName(), null);
                List<String>  primaryKeyNameList=getPrimayKeyName(conn,tableInfo.getTableName(),this.driverClass);
                Map<String,Integer>  columnLengthMap= getAllColoumnLength(conn,tableInfo.getDbName(),tableInfo.getTableName());
                while (result != null && result.next()) {
                    String columnName = result.getString("COLUMN_NAME");
                    String typeName = result.getString("TYPE_NAME");
                    // 获取字段长度方法修改,result.getString("COLUMN_SIZE")==null?null:Integer.parseInt(result.getString("COLUMN_SIZE"));
                    Integer  columnSize=columnLengthMap.get(columnName);
                    String  isNullable=result.getString("IS_NULLABLE")==null?null:result.getString("IS_NULLABLE");
                    Integer isNullabelInt=null;
                    if(isNullable!=null&&isNullable.equalsIgnoreCase("NO")){
                        isNullabelInt=0;
                    }else{
                        isNullabelInt=1;
                    }

                    CollectionTableField tableField = new CollectionTableField();
                    //设置是否主键
                    //设置是否主键
                    if (CollectionUtil.isNotEmpty(primaryKeyNameList) && primaryKeyNameList.contains(columnName)) {
                        tableField.setPrimaryKey(1);
                    }else {
                        tableField.setPrimaryKey(0);
                    }
                    //设置字段长度
                    tableField.setFieldLength(columnSize);
                    //设置字段名称
                    tableField.setFieldName(columnName);
                    //设置字段类型
                    tableField.setFieldType(typeName);
                    //设置是否为空
                    tableField.setFieldIsNull(isNullabelInt);
                    //设置字段描述
                    tableField.setFieldDesc(result.getString("REMARKS"));
                    //外部采集的数据，肯定没有分区字段
                    tableField.setIsPartition(0);
                    list.add(tableField);
                }


            }
        } catch (Exception e) {
            log.error("", e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("", e);
            }
        }

        //设置默认数据密级
        return getCollectionTableFields(tableInfo, list);
    }

    List<CollectionTableField> getCollectionTableFields(CollectionTableInfo tableInfo, List<CollectionTableField> list) {
        if (CollectionUtil.isNotEmpty(list)) {
            for (CollectionTableField collectionTableField : list) {
                Integer isPartition = collectionTableField.getIsPartition();
                //设置是否分区默认值
                if (isPartition == null) {
                    isPartition = 0;
                    collectionTableField.setIsPartition(isPartition);
                }
                String securityDataLevel = tableInfo.getSecurityDataLevel();
                if (StringUtils.isNotBlank(securityDataLevel)) {
                    collectionTableField.setSecurityDataLevel(securityDataLevel);
                }
            }
        }
        return list;
    }

    protected CollectionTableInfo getRowsAndLength(CollectionTableInfo tableInfo) {
        Connection conn = null;
        try {
            if (StringUtils.isEmpty(rowAndLengthSql)) {
                return null;
            }
            String rowAndLengthSql2 = rowAndLengthSql.replace("#{dbName}", tableInfo.getDbName()).replace("#{tableName}", tableInfo.getTableName());

            conn = getConnection();
            if (conn == null)
                throw new RRException("获取数据库连接失败");

            conn.setCatalog(tableInfo.getDbName());

            PreparedStatement sta = conn.prepareStatement(rowAndLengthSql2);
            ResultSet result = sta.executeQuery();
            while (result.next()) {
                if (result != null) {
                    CollectionTableInfo returnTable = new CollectionTableInfo();
                    returnTable.setRowsNum(result.getLong("tableRows"));
                    returnTable.setTotalSize(result.getDouble("dataLength"));
                    return returnTable;
                }
            }
        } catch (Exception e) {
            log.error("获取表大小错误:",e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }

        return null;
    }


    protected Connection getConnection(){
        String jdbcUrl = url;
        if (ConnectTypeEnum.MySQL.getDriverClass().equals(driverClass)) {
            jdbcUrl = url + "?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        }
        return SingleConnect.getInstance().getConnection(jdbcUrl, username, password, driverClass);
    }

    public String getUrl() {
        return url;
    }

    public String getDbName() {
        return dbName;
    }


    /**
    * @Description 获取主键名
    * @Author  chengweiping
    * @Date   2021/3/26 12:57
    */
    protected List<String> getPrimayKeyName(Connection conn,String tableName,String dbType) {
        List<String> primaryKeyNameList = new ArrayList<>();
        try {
            String tableNameNew=tableName;
            //根据表名获得主键结果集
            ResultSet pks =null;
            if(dbType!=null& ConnectTypeEnum.Oracle.getDriverClass().equals(dbType)){
                tableNameNew=tableName.toUpperCase(); // 对于oracle要将表名转为大写才能正确取出主键来
            }
            //获取结果集元数据
            pks =  conn.getMetaData().getPrimaryKeys(null, null, tableNameNew);
            //获取主键信息
            while (pks.next()) {
                String primaryKeyName=pks.getString(4);
                primaryKeyNameList.add(primaryKeyName);
                System.out.println("表:"+tableName+",主键："+primaryKeyName);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return primaryKeyNameList;
    }

    /**
    * @Description 获取字段长度
    * @Author  chengweiping
    * @Date   2021/3/28 15:53
    */
    protected Map<String,Integer> getAllColoumnLength(Connection conn,String dbName,String tableName) {
        Map<String,Integer> map=new HashMap<>();
        PreparedStatement pst = null;
        try {
            String sql="select * from %s.%s  where 1=2 ";
            String formatSql = String.format(sql,dbName,tableName);
            pst = conn.prepareStatement(formatSql);
            ResultSetMetaData rsd = pst.executeQuery().getMetaData();
            for(int i = 0; i < rsd.getColumnCount(); i++) {
                Integer  columnIndex=i+1;

                String columnName =rsd.getColumnName(columnIndex);
             //   String typeName = rsd.getColumnTypeName(columnIndex);*/
                Integer  columnSize=rsd.getColumnDisplaySize(columnIndex);
                map.put(columnName,columnSize);
              /*  Integer  isNullable=rsd.isNullable(columnIndex);
                System.out.print("  字段名称:"+columnName);
                System.out.print("  字段长度:"+rsd.getColumnDisplaySize(columnSize));*/

            }
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pst.closeOnCompletion();
            } catch(SQLException e) {
              e.printStackTrace();
            }
        }
        return map;
    }

}
